#!/bin/bash

# 定义变量方便修改
table_db=cy_database.
hive=/home/software/hive-2.3.6/bin/hive

# 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n "$1" ] ;then
	do_date=$1
else
	do_date=`date -d "-1 day" +%F`
fi

sql="
with
tmp1_table1 as 1 求和招聘人数   2 join企业详情表
(
select
	ent_name,
	sum(posts) as posts_amount
from dws_enterprise_position_require_detail
group by ent_name
),


tmp1_table2 as
(
select
	ei.ent_name,
	ei.uniform_social_credit_code,
	ei.business_nature,
	concat_ws('-',ei.province_code,ei.city_code,ei.area_code) as ent_address,
	t1.posts_amount,
	ei.industry_code,
	ei.business_code,
	ei.scale,
    cast(current_date() as string) as update_time
from tmp1_table1 t1
join dws_enterprise_info ei on t1.ent_name = ei.ent_name

),


tmp3_table as
(

),

tmp4_table as

)


insert overwrite table cy_database.ads_big_screen_result_ent_info
select ent_name,uniform_social_credit_code,business_nature,ent_address,posts_amount,industry_code,business_code,scale,update_time from tmp1_table2

;
"
$hive -e "$sql"